using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using Athena.Core;
using Athena.Objects;

using System.IO;
using System.Reflection;

namespace Athena.IO
{
    public class SQL
    {

        static SqlCeConnection _oConnection;
        static SqlCeEngine _Engine;

        private static void CreateDataBase(string connString)
        {
            try
            {
                _Engine.CreateDatabase();
                // now that we have a db, open a connection to it
                _oConnection = new SqlCeConnection(connString);
                _oConnection.Open();

                // create the parts table
                SqlCeCommand oCommand = _oConnection.CreateCommand();// sequence was nvarchar(100000)
                oCommand.CommandText = "CREATE TABLE Parts (PartID nvarchar(50) primary key, Name nvarchar(50), Type nvarchar(50), Sequence ntext, Direction nvarchar(10), Version nvarchar(20))";
                oCommand.ExecuteNonQuery();

                // create the species table
                oCommand = _oConnection.CreateCommand();
                oCommand.CommandText = "CREATE TABLE Species (SpeciesID nvarchar(50) primary key, Name nvarchar(50), Type nvarchar(50), Concentration float)";
                oCommand.ExecuteNonQuery();

                // create the connections table
                oCommand.CommandText = "CREATE TABLE Inputs (ReactionID nvarchar(50), ReactionName nvarchar(50), NodeID nvarchar(50), NodeName nvarchar(50), NodeType nvarchar(50), Stoichiometry float)";
                oCommand.ExecuteNonQuery();

                oCommand.CommandText = "CREATE TABLE Outputs (ReactionID nvarchar(50), ReactionName nvarchar(50), NodeID nvarchar(50), NodeName nvarchar(50), NodeType nvarchar(50), Stoichiometry float)";
                oCommand.ExecuteNonQuery();

                oCommand.CommandText = "CREATE TABLE Reactions (ReactionID nvarchar(50) primary key, Name nvarchar(50), Rate nvarchar(1000), Regulation nchar, Annotation nvarchar(1000))";
                oCommand.ExecuteNonQuery();

                /*
                // create the annotation table
                oCommand.CommandText = "CREATE TABLE Annotations (PartID nvarchar(50) primary key, Author nvarchar(500), Version nvarchar(500), Species nvarchar(20), Annotation nvarchar(2000))";
                oCommand.ExecuteNonQuery();*/

                ReadTabResource();
            }
            catch (Exception)
            {
            }
        }
        /// <summary>
        /// Initialize the Database. Creates a new database if the filename does not exist
        /// </summary>
        /// <param name="DBname"></param>
        public static void InitializeDB(string DBname)
        {
            //make the database
            if (!File.Exists(DBname))
            {                
                /*DBname = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "/" + DBname;
            }
            else
            {*/
                DBname = new FileInfo(DBname).FullName;
            }
            string connString = "Data Source='" + DBname + "'; Password=\"visualEditor\"; Encrypt = TRUE;";
            _Engine = new SqlCeEngine(connString);
            //_Engine.Upgrade();
            try
            {
                _oConnection = new SqlCeConnection(connString);
                _oConnection.Open();
                return;               
            }
            catch (Exception)  //database already exists
            {
                if (System.Windows.Forms.MessageBox.Show("The Parts and Reaction Database could not be opened and will be regenerated. This operation will take a while.", "Regenerate Parts Database", System.Windows.Forms.MessageBoxButtons.OKCancel, System.Windows.Forms.MessageBoxIcon.Information) == System.Windows.Forms.DialogResult.OK)
                {
                    try
                    {
                        if (File.Exists(DBname))
                            File.Delete(DBname);
                    }
                    catch (Exception)
                    {

                    }
                    CreateDataBase(connString);
                    InitializeDB(DBname);
                }
            }

            
        }

        private static void ReadTabResource()
        {
            //StringReader oReader = new StringReader(global::Athena.Properties.Resources.Registry);

            ReadTabFile(Utility.CurrentDirectory + "\\DB\\list.txt");
            //oReader.Close();

            
        }
        /// <summary>
        /// Open the database so that it can be written to
        /// </summary>
        /// <param name="DBname"></param>
        public static void OpenDB(string DBname)
        {
            if (!File.Exists(DBname))
            {
                DBname = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "/" + DBname;
            }
            else
            {
                DBname = new FileInfo(DBname).FullName;
            } string connString = "Data Source='" + DBname + "'; Password=\"visualEditor\"; Encrypt = TRUE;";
            _Engine = new SqlCeEngine(connString);
            _oConnection = new SqlCeConnection(connString);
            _oConnection.Open();
        }
        /// <summary>
        /// Close the database connection
        /// </summary>
        public static void CloseDB()
        {

            _oConnection.Close();
            _Engine.Shrink();
        }

        /// <summary>
        /// Insert an item into database. This method calls either InsertEdge, InsertSpecies, or InsertPart
        /// </summary>
        /// <param name="item"></param>
        public static void InsertItem(Item item)
        {
            if (item is Edge)
                InsertEdge((Edge)item);
            else
                if (item is Species)
                    InsertSpecies((Species)item);
                else
                    if (item is Part)
                        InsertPart((Part)item);
        }

        /// <summary>
        /// Insert a species into the database -- inserts the id, type, and concentration
        /// </summary>
        /// <param name="species"></param>
        public static void InsertSpecies(Species species)
        {
            try
            {
                SqlCeCommand oCommand = _oConnection.CreateCommand();
                oCommand.Parameters.AddWithValue("@id", species.DBID);
                oCommand.Parameters.AddWithValue("@label", species.ID);
                oCommand.Parameters.AddWithValue("@type", species.ItemType);
                oCommand.Parameters.AddWithValue("@concentration", species.Concentration);

                oCommand.CommandText = "UPDATE Species SET Name = @label, Type = @type, Concentration = @concentration WHERE SpeciesID = @id";
                oCommand.Prepare();
                int affectedRows = oCommand.ExecuteNonQuery();

                if (affectedRows < 1)
                {
                    oCommand.CommandText = "INSERT INTO Species (SpeciesID, Name, Type, Concentration) VALUES (@id, @label, @type, @concentration)";
                    oCommand.Prepare();
                    oCommand.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
            }
        }

        /// <summary>
        /// Insert a part into the database -- inserts ID, Type, Sequence, Direction, Version
        /// </summary>
        /// <param name="part"></param>
        public static void InsertPart(Part part)
        {
            try
            {
                SqlCeCommand oCommand = _oConnection.CreateCommand();
                
                oCommand.Parameters.AddWithValue("@id", part.DBID);
                oCommand.Parameters.AddWithValue("@label", part.ID);
                oCommand.Parameters.AddWithValue("@type", part.ItemType);
                //oCommand.Parameters.AddWithValue("@seq", part.Sequence);
                SqlCeParameter oParam = oCommand.Parameters.Add("@seq", System.Data.SqlDbType.NText);
                oParam.Value = part.Sequence;

                oCommand.Parameters.AddWithValue("@dir", part.Direction.ToString());
                oCommand.Parameters.AddWithValue("@version", part.Description.Version);

                oCommand.CommandText = "UPDATE Parts SET Name = @label, Type = @type, Sequence = @seq, Direction = @dir, Version = @version WHERE PartID = @id";
                oCommand.Prepare();
                int affectedRows = oCommand.ExecuteNonQuery();

                if (affectedRows < 1)
                {
                    oCommand.CommandText = "INSERT INTO Parts (PartID, Name, Type, Sequence, Direction, Version) VALUES (@id, @label, @type, @seq, @dir, @version)";
                    oCommand.Prepare();
                    oCommand.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
            }
        }

        /// <summary>
        /// Inserts a Reaction into the Reactions, Inputs, and Outputs tables
        /// </summary>
        /// <param name="edge"></param>
        public static void InsertEdge(Edge edge)
        {

            SqlCeCommand oCommand = _oConnection.CreateCommand();
            //insert reaction's rates and description

            oCommand.Parameters.Clear();
            oCommand.Parameters.AddWithValue("@id", edge.DBID);
            oCommand.Parameters.AddWithValue("@label", edge.ID);
            oCommand.Parameters.AddWithValue("@ratef", edge.Rate);
            oCommand.Parameters.AddWithValue("@regulation", edge._ReactionType);
            oCommand.Parameters.AddWithValue("@annotation", "");

            try // try to change values of the reaction
            {
                oCommand.CommandText = "UPDATE Reactions SET Rate = @ratef, Regulation = @regulation, Annotation = @annotation, Name = @label WHERE ReactionID = @id";
                oCommand.Prepare();
                int affectedRows = oCommand.ExecuteNonQuery();
                if (affectedRows < 1)
                {
                    oCommand.CommandText = "INSERT INTO Reactions (ReactionID, Name, Rate, Regulation, Annotation) VALUES (@id, @label, @ratef, @regulation, @annotation)";
                    oCommand.Prepare();
                    oCommand.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
            }
            

            //insert reactionID and nodeID for each input
            Node[] inputs = edge.Reactants;
            //List<double> inputCoeffs = edge.InputStoichiometry;

            for (int i = 0; i < inputs.Length; ++i)
            {
                Node node = inputs[i];
                double stoichiometry = edge.GetReactantStoichiometry(node);// inputCoeffs[i];

                oCommand.Parameters.Clear();
                oCommand.Parameters.AddWithValue("@rxid", edge.DBID);
                oCommand.Parameters.AddWithValue("@rxname", edge.ID);
                oCommand.Parameters.AddWithValue("@nodeid", node.DBID);
                oCommand.Parameters.AddWithValue("@nodename", node.ID);
                oCommand.Parameters.AddWithValue("@nodetype", node.ItemType);
                oCommand.Parameters.AddWithValue("@coeff", stoichiometry);

                try //try to update if the reaction already exists
                {
                    oCommand.CommandText = "UPDATE Inputs SET Stoichiometry = @coeff, ReactionName = @rxname, NodeName = @nodename WHERE ReactionID = @rxid AND NodeID = @nodeid AND NodeType = @nodetype";
                    oCommand.Prepare();
                    int affectedRows = oCommand.ExecuteNonQuery();
                    if (affectedRows < 1)
                    {
                        oCommand.CommandText = "INSERT INTO Inputs (ReactionID, ReactionName, NodeID, NodeName, NodeType, Stoichiometry) VALUES (@rxid, @rxname, @nodeid, @nodename, @nodetype, @coeff)";
                        oCommand.Prepare();
                        oCommand.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                }

            }

            //insert reactionID and nodeID for each output
            Node[] outputs = edge.Products;
            //List<double> outputCoeffs = edge.OutputStoichiometry;

            for (int i = 0; i < outputs.Length; ++i)
            {
                Node node = outputs[i];
                double stoichiometry = edge.GetProductStoichiometry(node);// outputCoeffs[i];

                oCommand.Parameters.Clear();
                oCommand.Parameters.AddWithValue("@rxid", edge.DBID);
                oCommand.Parameters.AddWithValue("@rxname", edge.ID);
                oCommand.Parameters.AddWithValue("@nodeid", node.DBID);
                oCommand.Parameters.AddWithValue("@nodename", node.ID);
                oCommand.Parameters.AddWithValue("@nodetype", node.ItemType);
                oCommand.Parameters.AddWithValue("@coeff", stoichiometry);

                try //try to update if the reaction already exists
                {
                    oCommand.CommandText = "UPDATE Outputs SET Stoichiometry = @coeff, ReactionName = @rxname, NodeName = @nodename WHERE ReactionID = @rxid AND NodeID = @nodeid AND NodeType = @nodetype";
                    oCommand.Prepare();
                    int affectedRows = oCommand.ExecuteNonQuery();
                    if (affectedRows < 1)
                    {
                        oCommand.CommandText = "INSERT INTO Outputs (ReactionID, ReactionName, NodeID, NodeName, NodeType, Stoichiometry) VALUES (@rxid, @rxname, @nodeid, @nodename, @nodetype, @coeff)";
                        oCommand.Prepare();
                        oCommand.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                }
            }
        }
        /// <summary>
        /// Given a set of nodes, this method searches the database for all reactions that involve those nodes and returns a list of those reactions
        /// </summary>
        /// <param name="nodes"></param>
        /// <returns></returns>
        public static List<Edge> LoadReactionsFromDB(List<Node> nodes)
        {
            StringBuilder list = new StringBuilder();
            list.Append("(");
            foreach (Node node in nodes)
            {
                list.Append("'");
                list.Append(node.DBID);
                list.Append("'");
                list.Append(",");

                /*string id = "@id" + i;
                list.Append(id);
                list.Append(",");
                oCommand.Parameters.AddWithValue(id, node.ID);
                ++i;*/
            }
            list.Remove(list.Length - 1, 1);
            list.Append(")");

            SqlCeCommand oCommand = _oConnection.CreateCommand();
            //get all the unique reactions involving this set of nodes
            //_oCommand.CommandText = "SELECT distinct ReactionID FROM ( (SELECT ReactionID FROM Inputs WHERE NodeID in " + list.ToString() + ") INTERSECT (SELECT ReactionID FROM Outputs WHERE NodeID in " + list.ToString() + ") )";
            oCommand.CommandText = "SELECT ReactionID,Name FROM Reactions WHERE ReactionID in (SELECT ReactionID FROM Inputs WHERE NodeID in " + list.ToString() + ") AND ReactionID in (SELECT ReactionID FROM Outputs WHERE NodeID in " + list.ToString() + ")";
            SqlCeDataReader oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

            List<string> ReactionIDs = new List<string>(), ReactionLabels = new List<string>();
            List<Edge> Edges = new List<Edge>();

            //place list of reaction IDs from the SQL query into a list
            while (oReader.Read())
            {
                ReactionIDs.Add(oReader.GetString(0));
                ReactionLabels.Add(oReader.GetString(1));
            }


            //for each reaction, get the input and output nodes
            for (int i=0; i < ReactionIDs.Count; ++i)
            {
                string reactionID = ReactionIDs[i],
                       reactionLabel = ReactionLabels[i];

                oCommand.Parameters.Clear();
                oCommand.Parameters.AddWithValue("@id", reactionID);

                oCommand.CommandText = "SELECT NodeID,NodeName,Stoichiometry FROM Inputs WHERE ReactionID = @id";  //INPUTS for this reaction
                oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                string ID, label;
                double stoic;
                List<Node> inputs = new List<Node>();
                List<double> inputStoichiometry = new List<double>();

                while (oReader.Read())  //for each input ID
                {
                    ID = oReader.GetString(0);
                    label = oReader.GetString(1);
                    stoic = oReader.GetDouble(2);
                    foreach (Node node in nodes)    //search each node
                    {
                        if (node.DBID.Equals(ID))  //if it has the same ID
                        {
                            node.ID = label;
                            inputs.Add(node);    //add it to the list of inputs
                            inputStoichiometry.Add((double)stoic);
                            break;
                        }
                    }
                }

                oCommand.CommandText = "SELECT NodeID,NodeName,Stoichiometry FROM Outputs WHERE ReactionID = @id";  //OUTPUTS for this reaction
                oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                List<Node> outputs = new List<Node>();
                List<double> outputStoichiometry = new List<double>();

                while (oReader.Read())  //for each output ID
                {
                    ID = oReader.GetString(0);
                    label = oReader.GetString(1);
                    stoic = oReader.GetDouble(2);
                    foreach (Node node in nodes)    //search each node
                    {
                        if (node.DBID.Equals(ID))  //if it has the same ID
                        {
                            node.ID = label;
                            outputs.Add(node);    //add it to the list of outputs
                            outputStoichiometry.Add((double)stoic);
                            break;
                        }
                    }
                }

                Edge edge = new Edge(inputs, outputs);
                edge.ID = reactionLabel;
                edge.DBID = reactionID;
                edge.SetReactantsStoichiometry(inputs.ToArray(), inputStoichiometry.ToArray());
                edge.SetProductsStoichiometry(outputs.ToArray(), outputStoichiometry.ToArray());

                try
                {
                    oCommand.CommandText = "SELECT Rate FROM Reactions WHERE ReactionID = @id";  //Rates
                    oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                    string Rate = oReader.GetString(0);

                    //now that we have the list of input and output nodes, make the reaction                
                    edge.Rate = (Rate);
                }
                catch (Exception)
                {
                }

                bool unique = true;

                foreach (Edge e in Edges)
                    if (e.ID.Equals(edge.DBID))
                    {
                        unique = false;
                        break;
                    }

                if (unique)
                    Edges.Add(edge);   //add to the final list
            }
            return Edges;
        }
        /// <summary>
        /// Given a set of nodes, this method searches the database for all reactions that involve those nodes and returns a list of those reactions.
        /// Different from LoadReactionsFromDB, because this method returns IDrawables. 
        /// </summary>
        /// <param name="nodes"></param>
        /// <returns></returns>
        public static List<BezierConnector> LoadConnectionsFromDB(List<IDrawable> drawables)
        {
            StringBuilder list = new StringBuilder();
            list.Append("(");
            foreach (IDrawable node in drawables)
            {
                if (node is IGlyph)
                {
                    list.Append("'");
                    list.Append(node.DBID);
                    list.Append("'");
                    list.Append(",");
                }
                /*string id = "@id" + i;
                list.Append(id);
                list.Append(",");
                oCommand.Parameters.AddWithValue(id, node.ID);
                ++i;*/
            }
            list.Remove(list.Length - 1, 1);
            list.Append(")");

            SqlCeCommand oCommand = _oConnection.CreateCommand();
            //get all the unique reactions involving this set of nodes
            //_oCommand.CommandText = "SELECT distinct ReactionID FROM ( (SELECT ReactionID FROM Inputs WHERE NodeID in " + list.ToString() + ") INTERSECT (SELECT ReactionID FROM Outputs WHERE NodeID in " + list.ToString() + ") )";
            oCommand.CommandText = "SELECT ReactionID,Name FROM Reactions WHERE ReactionID in (SELECT ReactionID FROM Inputs WHERE NodeID in " + list.ToString() + ") AND ReactionID in (SELECT ReactionID FROM Outputs WHERE NodeID in " + list.ToString() + ")";
            SqlCeDataReader oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

            List<string> ReactionIDs = new List<string>(), ReactionLabels = new List<string>();
            List<BezierConnector> Connectors = new List<BezierConnector>();

            //place list of reaction IDs from the SQL query into a list
            while (oReader.Read())
            {
                ReactionIDs.Add(oReader.GetString(0));
                ReactionLabels.Add(oReader.GetString(1));
            }


            //for each reaction, get the input and output nodes
            for (int i=0; i < ReactionIDs.Count; ++i)
            {
                string reactionID = ReactionIDs[i],
                       reactionLabel = ReactionLabels[i];

                oCommand.Parameters.Clear();
                oCommand.Parameters.AddWithValue("@id", reactionID);

                oCommand.CommandText = "SELECT NodeID,NodeName,NodeType,Stoichiometry FROM Inputs WHERE ReactionID = @id";  //INPUTS for this reaction
                oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                string ID,label,type;
                double stoic;
                List<IDrawable> inputs = new List<IDrawable>();
                List<double> inputStoichiometry = new List<double>();

                while (oReader.Read())  //for each input ID
                {
                    ID = oReader.GetString(0);
                    label = oReader.GetString(1);
                    type = oReader.GetString(2);
                    stoic = oReader.GetDouble(3);
                    foreach (IDrawable node in drawables)    //search each node
                    {
                        if (node.DBID.Equals(ID) && node.Type.Equals(type))  //if it has the same ID
                        {
                            node.Name = label;
                            inputs.Add(node);    //add it to the list of inputs
                            inputStoichiometry.Add((double)stoic);
                            break;
                        }
                    }
                }

                oCommand.CommandText = "SELECT NodeID,NodeName,NodeType,Stoichiometry FROM Outputs WHERE ReactionID = @id";  //OUTPUTS for this reaction
                oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                List<IDrawable> outputs = new List<IDrawable>();
                List<double> outputStoichiometry = new List<double>();

                while (oReader.Read())  //for each output ID
                {
                    ID = oReader.GetString(0);
                    label = oReader.GetString(1);
                    type = oReader.GetString(2);
                    stoic = oReader.GetDouble(3);
                    foreach (IDrawable node in drawables)    //search each node
                    {
                        if (node.DBID.Equals(ID) && node.Type.Equals(type))  //if it has the same ID
                        {
                            node.Name = label;
                            outputs.Add(node);    //add it to the list of outputs
                            outputStoichiometry.Add((double)stoic);
                            break;
                        }
                    }
                }

                BezierConnector connector = new BezierConnector(inputs, outputs);
                connector.DBID = reactionID;
                connector.Text = connector.Name = reactionLabel;

                for (int j = 0; j < inputs.Count; ++j)
                {
                    if (inputStoichiometry.Count > j)
                        connector.SetReactantStoichiometry(inputs[j], inputStoichiometry[j],false);
                }
                for (int j = 0; j < inputs.Count; ++j)
                {
                    if (outputStoichiometry.Count > j)
                        connector.SetReactantStoichiometry(outputs[j], outputStoichiometry[j], false);
                }

                try
                {
                    oCommand.CommandText = "SELECT Rate FROM Reactions WHERE ReactionID = @id";  //Rates
                    oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                    string Rate = oReader.GetString(0);

                    //now that we have the list of input and output nodes, make the reaction                
                    if (connector.Item is Edge)
                    {
                        ((Edge)connector.Item).Rate = (Rate);
                    }
                }
                catch (Exception)
                {
                }

                bool unique = true;
                foreach (Connector c in Connectors)
                    if (c.Name.Equals(connector.DBID))
                    {
                        unique = false;
                        break;
                    }

                if (unique)
                    Connectors.Add(connector);   //add to the final list
            }
            return Connectors;
        }
        /// <summary>
        /// Remove this item from the database. Warning: this will NOT remove the reactions involving this node (if it is a Node)
        /// </summary>
        /// <param name="item"></param>
        public static void DeleteFromDB(Item item)
        {
            if (item is Edge)
            {
                RemoveEdge((Edge)item);
                return;
            }
            if (item is Part)
            {
                RemovePart((Part)item);
                return;
            }
            if (item is Species)
            {
                RemoveSpecies((Species)item);
                return;
            }
        }

        public static void RemoveSpecies(Species species)
        {
            SqlCeCommand oCommand = _oConnection.CreateCommand();
            oCommand.Parameters.AddWithValue("@id", species.DBID);
            oCommand.CommandText = "DELETE FROM Species WHERE PartID = @id";
            oCommand.ExecuteNonQuery();
        }
        public static void RemovePart(Part part)
        {
            SqlCeCommand oCommand = _oConnection.CreateCommand();
            oCommand.Parameters.AddWithValue("@id", part.DBID);
            oCommand.CommandText = "DELETE FROM Parts WHERE PartID = @id";
            oCommand.ExecuteNonQuery();
        }
        public static void RemoveEdge(Edge edge)
        {
            SqlCeCommand oCommand = _oConnection.CreateCommand();
            oCommand.Parameters.AddWithValue("@id", edge.DBID);

            oCommand.CommandText = "DELETE FROM Inputs WHERE ReactionID = @id";
            oCommand.ExecuteNonQuery();

            oCommand.CommandText = "DELETE FROM Outputs WHERE ReactionID = @id";
            oCommand.ExecuteNonQuery();

            oCommand.CommandText = "DELETE FROM Reactions WHERE ReactionID = @id";
            oCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Returns a list of nodes that can be substituted in place of the given node. The match is based on what reactions
        /// the two nodes partake in.
        /// </summary>
        /// <param name="node"></param>
        /// <returns></returns>
        public static List<Node> Substitutes(Node node)
        {
            List<Node> nodes = PossibleSubstitutes(node);
            List<Node> properNodes = new List<Node>();
            
            SqlCeCommand oCommand = _oConnection.CreateCommand();

            foreach (Node n in nodes)
            {
                Node n2 = null;
                if (n is Species && !string.IsNullOrEmpty(n.DBID))
                {
                    oCommand.Parameters.Clear();
                    oCommand.Parameters.AddWithValue("@id", n.DBID);
                    oCommand.CommandText = "SELECT Name,Type,Concentration FROM Species WHERE SpeciesID = @id";
                    SqlCeDataReader oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                    oReader.Read();
                    if (oReader.FieldCount > 0)
                    {
                        string label = oReader.GetString(0),
                               type = oReader.GetString(1);
                        double conc = oReader.GetDouble(2);

                        n2 = new Species(type, label, conc);
                        n2.DBID = n.DBID;
                    }
                }
                else
                    if (n is Part && !string.IsNullOrEmpty(n.DBID))
                    {
                        oCommand.Parameters.Clear();
                        oCommand.Parameters.AddWithValue("@id", n.DBID);
                        oCommand.CommandText = "SELECT Name,Type,Direction,Sequence FROM Parts WHERE PartID = @id";
                        SqlCeDataReader oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                        oReader.Read();
                        if (oReader.FieldCount > 0)
                        {
                            string label = oReader.GetString(0),
                                   type = oReader.GetString(1),
                                   dir = oReader.GetString(2),
                                   seq = oReader.GetString(3);

                            n2 = new Part(type, label, seq);
                            if (dir == Part.Orientation.reverse.ToString())
                                ((Part)n2).Direction = Part.Orientation.reverse;
                            else
                                ((Part)n2).Direction = Part.Orientation.forward;
                            n2.DBID = n.DBID;
                        }
                    }
                if (n2 != null)
                    properNodes.Add(n2);
            }
            return properNodes;
        }

        private static List<Node> PossibleSubstitutes(Node node)
        {
            SqlCeCommand oCommand = _oConnection.CreateCommand();

            List<Node> candidateInputs = new List<Node>(),
                       candidateOutputs = new List<Node>();

            //get all the reactions that this node currently partakes in
            List<Edge> incomingEdges = node.IncomingEdges,
                       outgoingEdges = node.OutgoingEdges;

            //remove X->waste and source->X from the edge list. It is not in the database, so it will cause trouble
            for (int i = 0; i < outgoingEdges.Count; ++i)
            {
                Node[] outputs = outgoingEdges[i].Products;
                if ((outputs.Length == 1 &&
                    (outputs[0] == Core.Module.Empty)) || outputs.Length == 0)
                {
                    outgoingEdges.RemoveAt(i);
                    --i;
                }
            }
            for (int i = 0; i < incomingEdges.Count; ++i)
            {
                Node[] inputs = incomingEdges[i].Reactants;
                if ((inputs.Length == 1 &&
                    (inputs[0] == Core.Module.Empty)) || inputs.Length == 0)
                {
                    incomingEdges.RemoveAt(i);
                    --i;
                }
            }

            bool noDefinedInteractions = true;

            if (noDefinedInteractions)
                foreach (Edge e in incomingEdges)
                {
                    foreach (Node n in e.Reactants)
                    {
                        if (!string.IsNullOrEmpty(n.DBID))
                        {
                            noDefinedInteractions = false;
                            break;
                        }
                    }
                    if (!noDefinedInteractions)
                        break;
                }

            if (noDefinedInteractions)
                foreach (Edge e in outgoingEdges)
                {
                    foreach (Node n in e.Products)
                    {
                        if (!string.IsNullOrEmpty(n.DBID))
                        {
                            noDefinedInteractions = false;
                            break;
                        }
                    }
                    if (!noDefinedInteractions)
                        break;
                }
            if (noDefinedInteractions || (incomingEdges.Count < 1 && outgoingEdges.Count < 1)) //find any substitute
            {
                if (node is Species)
                {
                    oCommand.Parameters.Clear();
                    oCommand.Parameters.AddWithValue("@type", node.ItemType);
                    oCommand.CommandText = "SELECT SpeciesID,Name FROM Species WHERE Type = @type";
                    SqlCeDataReader oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                    while (oReader.Read())  //for each candidate reaction
                    {
                        string speciesID = oReader.GetString(0), speciesLabel = oReader.GetString(1);
                        Node newNode = new Species("protein",speciesLabel,1);
                        newNode.DBID = speciesID;
                        candidateInputs.Add(newNode);
                    }
                }
                else
                    if (node is Part)
                    {
                        oCommand.Parameters.Clear();
                        oCommand.Parameters.AddWithValue("@type", node.ItemType);
                        oCommand.CommandText = "SELECT PartID,Name FROM Parts WHERE Type = @type";
                        SqlCeDataReader oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                        while (oReader.Read())  //for each candidate reaction
                        {
                            string partID = oReader.GetString(0), partLabel = oReader.GetString(1);
                            Node newNode = new Part("part", partLabel, "ATATATAT");
                            newNode.DBID = partID;
                            candidateInputs.Add(newNode);
                        }
                    }
                return candidateInputs;
            }

            //for each reaction that node partakes in, see if there is such a reaction in the database
            foreach (Edge e in outgoingEdges)
            {
                StringBuilder s = new StringBuilder();
                
                List<Node> prevCandidates = new List<Node>();
                prevCandidates.AddRange(candidateInputs);
                candidateInputs.Clear();

                //make a string of the form:
                //ReactionID in (SELECT Reaction ID FROM Inputs WHERE NodeID = id1) AND ReactionID in (SELECT Reaction ID FROM Inputs WHERE NodeID = id2) ...
                foreach (Node n in e.Reactants)
                {
                    if (n != node && !string.IsNullOrEmpty(n.DBID))
                    {
                        if (s.Length > 0)
                        {
                            s.Append(" AND ");
                        }
                        s.Append("ReactionID in (SELECT ReactionID FROM Inputs WHERE NodeID = '");
                        s.Append(n.DBID);
                        s.Append("' AND NodeType = '");
                        s.Append(n.ItemType);
                        s.Append("')");
                    }
                }
                //ReactionID in (SELECT Reaction ID FROM Outputs WHERE NodeID = id3) AND ReactionID in (SELECT Reaction ID FROM Outputs WHERE NodeID = id4) ...
                foreach (Node n in e.Products)
                {
                    if (n != node && !(string.IsNullOrEmpty(n.DBID)))
                    {
                        if (s.Length > 0)
                        {
                            s.Append(" AND ");
                        }
                        s.Append("ReactionID in (SELECT ReactionID FROM Outputs WHERE NodeID = '");
                        s.Append(n.DBID);
                        s.Append("' AND NodeType = '");
                        s.Append(n.ItemType);
                        s.Append("')");
                    }
                }

                if (s.Length < 1) continue;

                //get all the reactions with the same inputs and outputs as edge e (one of node's outgoing edges)
                oCommand.CommandText = "SELECT distinct ReactionID FROM Reactions WHERE " + s.ToString();
                SqlCeDataReader oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                
                //we now have a list of reactionIDs that contain all the inputs and outputs needed
                //we need to check that these are the ONLY inputs and outputs that this reaction has:

                while (oReader.Read())  //for each candidate reaction
                {
                    string reactionID = oReader.GetString(0);

                    oCommand.Parameters.Clear();
                    oCommand.Parameters.AddWithValue("@id",reactionID);
                    oCommand.CommandText = "SELECT NodeID FROM Outputs WHERE ReactionID = @id"; //get all its outputs
                    SqlCeDataReader oReader2 = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                    int numOutputs = 0;
                    while (oReader2.Read())  //read each output in that reaction
                    {
                        ++numOutputs;
                    }

                    if (numOutputs == e.Products.Length)  //number of outupts match, now lets check inputs
                    {
                        oCommand.Parameters.Clear();
                        oCommand.Parameters.AddWithValue("@id", reactionID);
                        oCommand.CommandText = "SELECT NodeID,NodeName FROM Inputs WHERE ReactionID = @id"; //get all its inputs
                        oReader2 = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                        List<string> inputIDs = new List<string>();  //IDs of the inputs nodes in this reaction
                        List<string> inputLabels = new List<string>();  //Labels of the inputs nodes in this reaction
                        List<string> edgeInputNames = new List<string>();  //IDs of inputs in this edge

                        foreach (Node n in e.Reactants) edgeInputNames.Add(n.DBID);  

                        int numInputs = 0;
                        while (oReader2.Read())  //read each input in that reaction
                        {
                            ++numInputs;
                            inputIDs.Add(oReader2.GetString(0));  //add the input node's name to the list
                            inputLabels.Add(oReader2.GetString(1)); 
                        }

                        if (numInputs == e.Reactants.Length)
                        {
                            //by this point, the edge e and this reaction are exactly the same
                            for (int i = 0; i < inputIDs.Count; ++i) //for each input in this reaction
                            {
                                bool accept = (prevCandidates.Count < 1); //if there are not candidate inputs, add to the list
                                if (!accept)
                                {
                                    foreach (Node n in prevCandidates)
                                    {
                                        if (n.DBID.Equals(inputIDs[i]))
                                        {
                                            accept = true;
                                            break;
                                        }
                                    }
                                }

                                if (accept && !(edgeInputNames.Contains(inputIDs[i])))  //there is an input in reaction (from DB) that is not in this edge
                                {
                                    Node n = (Node)node.Clone();
                                    n.DBID = inputIDs[i];
                                    n.ID = inputLabels[i];
                                    candidateInputs.Add(n);   //thats one candidate for the inputs
                                }
                            }
                        }
                    }

                }
            }

            //for each reaction that node partakes in, see if there is such a reaction in the database
            foreach (Edge e in incomingEdges)
            {
                StringBuilder s = new StringBuilder();

                List<Node> prevCandidates = new List<Node>();
                prevCandidates.AddRange(candidateOutputs);
                candidateOutputs.Clear();

                //make a string of the form:
                //ReactionID in (SELECT Reaction ID FROM Inputs WHERE NodeID = id1) AND ReactionID in (SELECT Reaction ID FROM Inputs WHERE NodeID = id2) ...
                foreach (Node n in e.Reactants)
                {
                    if (n != node && !string.IsNullOrEmpty(n.DBID))
                    {
                        if (s.Length > 0)
                        {
                            s.Append(" AND ");
                        }
                        s.Append("ReactionID in (SELECT ReactionID FROM Inputs WHERE NodeID = '");
                        s.Append(n.DBID);
                        s.Append("' AND NodeType = '");
                        s.Append(n.ItemType);
                        s.Append("')");
                    }
                }
                //ReactionID in (SELECT Reaction ID FROM Outputs WHERE NodeID = id3) AND ReactionID in (SELECT Reaction ID FROM Outputs WHERE NodeID = id4) ...
                foreach (Node n in e.Products)
                {
                    if (n != node && !string.IsNullOrEmpty(n.DBID))
                    {
                        if (s.Length > 0)
                        {
                            s.Append(" AND ");
                        }
                        s.Append("ReactionID in (SELECT ReactionID FROM Outputs WHERE NodeID = '");
                        s.Append(n.DBID);
                        s.Append("' AND NodeType = '");
                        s.Append(n.ItemType);
                        s.Append("')");
                    }
                }

                if (s.Length < 1) continue;

                //get all the reactions with the same inputs and outputs as edge e (one of node's outgoing edges)
                oCommand.CommandText = "SELECT distinct ReactionID FROM Reactions WHERE " + s.ToString();
                SqlCeDataReader oReader = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                //we now have a list of reactionIDs that contain all the inputs and outputs needed
                //we need to check that these are the ONLY inputs and outputs that this reaction has:

                while (oReader.Read())  //for each candidate reaction
                {
                    string reactionID = oReader.GetString(0);

                    oCommand.Parameters.Clear();
                    oCommand.Parameters.AddWithValue("@id", reactionID);
                    oCommand.CommandText = "SELECT NodeID,NodeName FROM Inputs WHERE ReactionID = @id"; //get all its outputs
                    SqlCeDataReader oReader2 = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                    int numInputs = 0;
                    while (oReader2.Read())  //read each output in that reaction
                    {
                        ++numInputs;
                    }

                    if (numInputs == e.Reactants.Length)  //number of outupts match, now lets check inputs
                    {
                        oCommand.Parameters.Clear();
                        oCommand.Parameters.AddWithValue("@id", reactionID);
                        oCommand.CommandText = "SELECT NodeID,NodeName FROM Outputs WHERE ReactionID = @id"; //get all its inputs
                        oReader2 = oCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

                        List<string> outputIDs = new List<string>();  //IDs of the inputs nodes in this reaction
                        List<string> outputLabels = new List<string>();  //Labels of the inputs nodes in this reaction                        
                        List<string> edgeOutputNames = new List<string>();  //IDs of inputs in this edge

                        foreach (Node n in e.Reactants) edgeOutputNames.Add(n.DBID);

                        int numOutputs = 0;
                        while (oReader2.Read())  //read each input in that reaction
                        {
                            ++numOutputs;
                            outputIDs.Add(oReader2.GetString(0));  //add the input node's name to the list
                            outputLabels.Add(oReader2.GetString(1)); 
                        }

                        if (numOutputs == e.Products.Length)
                        {
                            //by this point, the edge e and this reaction are exactly the same
                            for (int i = 0; i < outputIDs.Count; ++i )
                            {

                                bool accept = (prevCandidates.Count < 1); //if there are not candidate inputs, add to the list
                                if (!accept)
                                {
                                    foreach (Node n in prevCandidates)
                                    {
                                        if (n.DBID.Equals(outputIDs[i]))
                                        {
                                            accept = true;
                                            break;
                                        }
                                    }
                                }

                                if (accept && !(edgeOutputNames.Contains(outputIDs[i])))  //there is an input in reaction (from DB) that is not in this edge
                                {
                                    Node n = (Node)node.Clone();
                                    n.DBID = outputIDs[i];
                                    n.ID = outputLabels[i];
                                    candidateOutputs.Add(n);   //thats one candidate for the outputs
                                }
                            }
                        }
                    }

                }
            }

            if (outgoingEdges.Count == 0 || candidateInputs.Count == 0)
                return candidateOutputs;

            if (incomingEdges.Count == 0 || candidateOutputs.Count == 0)
                return candidateInputs;

            List<Node> candidates = new List<Node>();

            foreach (Node n in candidateInputs)
            {
                foreach (Node n2 in candidateOutputs)
                {
                    if (n.DBID.Equals(n2.DBID))
                    {
                        candidates.Add(n);           //take intersection
                        break;
                    }
                }
            }

            return candidates;
        }
  /*      
        /// <summary>
        /// Read a tab-delimited file of transcription factors and the promoter they regule
        /// the file's columns must be: 1)trans.fac 2)trans.fac gene 3)regulated promoter 4) regulation type
        /// the regulation type is - or +
        /// </summary>
        /// <param name="inputstream"></param>
        public static void ReadTFList(TextReader inputstream)
        {
            string line = inputstream.ReadLine();
            int i = 0;
            while (line != null)
            {
                ++i;

                string[] words = line.Split(new char[] { '\t' } , StringSplitOptions.RemoveEmptyEntries);
                Node tf_gene = new Gene(words[0], ""),
                     tf = new Species("protein", words[1], 0),
                     promoter = new Part("promoter",words[2], "");
                string regulation = words[3], strand = words[4], promoterSeq = words[5];

                Edge e1 = new Edge(tf, promoter, "");
                e1._ReactionType = words[3];
                e1.ID = (tf.ID + promoter.ID);

                Edge e2 = new Edge(tf_gene, tf, "0");
                e2.ID = (tf_gene.ID +  tf.ID);

                tf.DBID = tf.ID;
                tf_gene.DBID = tf_gene.ID;
                promoter.DBID = promoter.ID;

                InsertItem(tf);
                InsertItem(tf_gene);
                InsertItem(promoter);

                e1.DBID = "REG" + i;
                e2.DBID = "TRS" + i;
                InsertEdge(e1);
                InsertEdge(e2);

                line = inputstream.ReadLine();
            }
        }
   */ 
        public static void ReadTabFile(string filename)
        {
            try
            {
                Environment.CurrentDirectory = Utility.CurrentDirectory;

                StreamReader filereader = new StreamReader(new FileStream(filename, FileMode.Open, FileAccess.Read));
                StreamReader filereader2;
                string line;

                //gene file
                line = filereader.ReadLine();
                line.TrimEnd('\n', ' ');
                
                filereader2 = new StreamReader(new FileStream(line, FileMode.Open, FileAccess.Read));
                ReadGeneFile(filereader2);
                filereader2.Close();

                //rbs
                line = filereader.ReadLine();
                line.TrimEnd('\n', ' ');

                filereader2 = new StreamReader(new FileStream(line, FileMode.Open, FileAccess.Read));
                ReadRBSfile(filereader2);
                filereader2.Close();

                //terminator
                line = filereader.ReadLine();
                line.TrimEnd('\n', ' ');

                filereader2 = new StreamReader(new FileStream(line, FileMode.Open, FileAccess.Read));
                ReadTerminatorFile(filereader2);
                filereader2.Close();

                //gene to prot
                line = filereader.ReadLine();
                line.TrimEnd('\n', ' ');

                filereader2 = new StreamReader(new FileStream(line, FileMode.Open, FileAccess.Read));
                filereader2.ReadLine();  //skip header
                ReadGeneProductFile(filereader2);
                filereader2.Close();

                //prot to promoter
                line = filereader.ReadLine();
                line.TrimEnd('\n', ' ');

                filereader2 = new StreamReader(new FileStream(line, FileMode.Open, FileAccess.Read));
                ReadOperatorFile(filereader2);
                filereader2.Close();

                filereader.Close();
            }
            catch (Exception) { }
        } 
        

        public static void ReadRBSfile(StreamReader filereader)
        {
            filereader.ReadLine();
            string line;
            string[] words;
            while (!filereader.EndOfStream)
            {
                line = filereader.ReadLine();
                if (line.Length > 0)
                {
                    words = line.Split('\t');
                    Part rbs = new Part("rbs", words[1] + "rbs", words[5]);
                    rbs.DBID = words[0];
                    InsertPart(rbs);
                }
            }
        }

        public static void ReadPromoterFile(StreamReader filereader)
        {
            filereader.ReadLine();
            string line;
            string[] words;
            while (!filereader.EndOfStream)
            {
                line = filereader.ReadLine();
                if (line.Length > 0)
                {
                    words = line.Split('\t');
                    Part promoter = new Part("promoter", words[1], words[5]);

                    if (words[2].Equals("reverse"))
                        promoter.Direction = Part.Orientation.reverse;
                    else
                        promoter.Direction = Part.Orientation.forward;

                    promoter.DBID = words[0];
                    InsertPart(promoter);
                }
            }
        }

        public static void ReadTerminatorFile(StreamReader filereader)
        {
            filereader.ReadLine();
            string line;
            string[] words;
            while (!filereader.EndOfStream)
            {
                line = filereader.ReadLine();
                if (line.Length > 0)
                {
                    words = line.Split('\t');
                    Part promoter = new Part("terminator", words[5] + "end", words[4]);

                    if (words[3].Equals("reverse"))
                        promoter.Direction = Part.Orientation.reverse;
                    else
                        promoter.Direction = Part.Orientation.forward;

                    promoter.DBID = words[0];
                    InsertPart(promoter);
                }
            }
        }

        public static void ReadOperatorFile(StreamReader filereader)
        {
            string line, pair;
            string[] words;

            List<string> OperatorID = new List<string>(),
                         TfName = new List<string>(),
                         Pairs = new List<string>();

            while (!filereader.EndOfStream)
            {
                line = filereader.ReadLine();
                if (line.Length > 0)
                {
                    words = line.Split('\t');
                    pair = words[0] + "_" + words[1];
                    if (!Pairs.Contains(pair))
                    {
                        Pairs.Add(pair);

                        Species tf = new Species("protein", words[1], 1);
                        tf.DBID = words[1];

                        Part op = new Part("promoter", words[7], words[10]);
                        op.DBID = words[0];
                        if (words[5] == "reverse")
                            op.Direction = Part.Orientation.forward;
                        else
                            op.Direction = Part.Orientation.reverse;

                        /*if (!TfName.Contains(words[1]))
                        {
                            TfName.Add(words[1]);
                            InsertSpecies(tf);
                        }*/

                        if (!OperatorID.Contains(words[0]))
                        {
                            OperatorID.Add(words[0]);
                            InsertPart(op);
                        }

                        Edge e = new Edge(tf, op);
                        e.ID = tf.ID + "_" + op.ID;
                        e.DBID = tf.DBID + "_" + op.DBID;
                        if (words[8] == "repressor")
                        {
                            e._ReactionType = "-";
                            e.Rate = "1/(1 + " + tf.ID + ")";
                        }
                        else
                        {
                            e._ReactionType = "+";
                            e.Rate = tf.ID + "/(1 + " + tf.ID + ")";
                        }
                        InsertEdge(e);
                    }
                }
            }
        }

        public static void ReadGeneProductFile(StreamReader filereader)
        {
            string line, pair;
            string[] words;

            List<string> ProtName = new List<string>(),
                         GeneName = new List<string>(),
                         Pairs = new List<string>();

            while (!filereader.EndOfStream)
            {
                line = filereader.ReadLine();
                if (line.Length > 0)
                {
                    words = line.Split('\t');
                    pair = words[0] + "_" + words[1];
                    if (!Pairs.Contains(pair))
                    {
                        Pairs.Add(pair);

                        Species prot = new Species("protein", words[0], 1);
                        prot.DBID = words[0];

                        Part gene = new Part("gene", words[1], "");
                        gene.DBID = words[1];

                        if (!ProtName.Contains(words[0]))
                        {
                            ProtName.Add(words[0]);
                            InsertSpecies(prot);
                        }

                        Edge e = new Edge(gene, prot);
                        e.ID = gene.ID + "_" + prot.ID;
                        e.DBID = gene.DBID + "_" + prot.DBID;
                        InsertEdge(e);
                    }
                }
            }
        }

        public static void ReadGeneFile(StreamReader filereader)
        {
            filereader.ReadLine();
            string line, geneName = null, sequence = "";
            while (!filereader.EndOfStream)
            {
                line = filereader.ReadLine();
                if (line.Length > 0)
                {
                    if (line[0] == '>')
                    {
                        if (!string.IsNullOrEmpty(geneName))
                        {
                            Part gene = new Part("gene", geneName, sequence);//.Substring(0,Math.Min(1000,sequence.Length)));
                            gene.DBID = geneName;
                            InsertPart(gene);
                        }
                        geneName = line.Substring(1);
                        sequence = "";
                    }
                    else
                    {
                        line.Trim('\n', ' ','\r');
                        sequence = sequence + line;
                    }
                }
            }
        }
    }
}
